package com.yimi.yinhepda.db.dao;

import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.dao.RawRowMapper;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.Where;
import com.yimi.yinhepda.base.BaseApplication;
import com.yimi.yinhepda.db.helper.DbHelper;
import com.yimi.yinhepda.db.table.LineVoTable;
import com.pda.ym.utils.DateHelper;
import com.pda.uce.commom.util.LogUtil;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * @author 000432
 * @date 2017/4/12
 * 更新线路基础资料数据操作
 */
public class LineVoTableDao extends BaseDao<LineVoTable, Long> {
    private static final String TAG = SortScanTableDao.class.getSimpleName();

    public LineVoTableDao() {
        super(DbHelper.getInstance(BaseApplication.getContext()), LineVoTable.class);
    }

    public void createOrUpdate(List<LineVoTable> list) {
        SQLiteDatabase database = getDbHelper().getWritableDatabase();
        String tableName = dao.getTableName();
        StringBuffer sql = new StringBuffer("replace into ");
        sql.append(tableName);
        sql.append("(lineId,");
        sql.append("lineCode,");
        sql.append("lineName,");
        sql.append("sourceZoneCode,");
        sql.append("sourceZoneName,");
        sql.append("destZoneCode,");
        sql.append("destZoneName,");
        sql.append("transportWay,");
        sql.append("productType,");
        sql.append("areaType,");
        sql.append("businessModel,");
        sql.append("transportAttribute,");
        sql.append("performCycle,");
        sql.append("lineMileage,");
        sql.append("timeEffect,");
        sql.append("effectiveType,");
        sql.append("effectiveTime,");
        sql.append("invalidTime,");
        sql.append("note,");
        sql.append("compCode,");
        sql.append("creater,");
        sql.append("createrTime,");
        sql.append("modifier,");
        sql.append("modifierTime,");
        sql.append("isDelete,");
        sql.append("latestTime,");
        sql.append("recordVersion)");
        //27
        sql.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        LogUtil.e("sql=" + sql.toString());


        database.beginTransaction();
        Date currentDate = new Date();
        for (LineVoTable item : list) {
            if (!item.getLineName().contains("禁用") && item.getIsDelete() != 1) {
                int effectivtTime = DateHelper.compareToTime(currentDate, item.getEffectiveTime());
                int invalidTime = DateHelper.compareToTime(currentDate, item.getInvalidTime());
                if (effectivtTime >= 0 && invalidTime <= 0) {
                    Object[] values = new Object[]{
                            item.getLineId(), item.getLineCode(), item.getLineName(), item.getSourceZoneCode(), item.getSourceZoneName(), item.getDestZoneCode(),
                            item.getDestZoneName(), item.getTransportWay(), item.getProductType(), item.getAreaType(), item.getBusinessModel(),
                            item.getTransportAttribute(), item.getPerformCycle(), item.getLineMileage(), item.getTimeEffect(), item.getEffectiveType(),
                            item.getEffectiveTime(), item.getInvalidTime(), item.getNote(), item.getCompCode(), item.getCreater(), item.getCreaterTime(),
                            item.getModifier(), item.getModifierTime(), item.getIsDelete(), item.getLatestTime(), item.getRecordVersion()
                    };
                    database.execSQL(sql.toString(), values);
                }
            }
        }
        database.setTransactionSuccessful();
        database.endTransaction();
    }

    public List<LineVoTable> findDeptCode(String deptCode) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.or(where.isNull("invalidTime"), where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date())));
            where.and().le("effectiveTime", DateHelper.getDateTimeFormate(new Date()));
            where.and().eq("sourceZoneCode", deptCode);
            where.or().eq("destZoneCode", deptCode);
            //主用线路
            where.and().eq("transportAttribute", 1);
            //干线和支线
            where.and().ne("transportWay", 3);
            where.and().ne("isDelete", 1);

            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<LineVoTable> findSourceZoneCode(String sourceZoneCode) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.or(where.isNull("invalidTime"), where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date())));
            where.and().le("effectiveTime", DateHelper.getDateTimeFormate(new Date()));
            where.and().eq("sourceZoneCode", sourceZoneCode);
            //主用线路
            where.and().eq("transportAttribute", 1);
            //干线和支线
            where.and().ne("transportWay", 3);
            where.and().ne("isDelete", 1);

            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询下一站
     *
     * @param sourceZoneCode 上一站
     * @param destZone       下一站
     * @return
     */
    public List<LineVoTable> findNextLine(String sourceZoneCode, String destZone) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();

            Where where = queryBuilder.where();
            where.and(
                    where.le("effectiveTime", DateHelper.getDateTimeFormate(new Date())),
                    where.eq("sourceZoneCode", sourceZoneCode),
                    where.eq("transportAttribute", 1),
                    where.ne("transportWay", 3),
                    where.ne("isDelete", 1),
                    where.or(
                            where.isNull("invalidTime"),
                            where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date()))
                    )
            );
            if (destZone != null) {
                where.and(
                        where.or(
                                where.like("destZoneCode", "%" + destZone + "%"),
                                where.like("destZoneName", "%" + destZone + "%")
                        ),
                        null
                );
            }

            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询上一站
     *
     * @param sourceZone   上一站
     * @param destZoneCode 下一站
     * @return
     */
    public List<LineVoTable> findPreviousLine(String sourceZone, String destZoneCode) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();

            Where where = queryBuilder.where();
            where.and(
                    where.le("effectiveTime", DateHelper.getDateTimeFormate(new Date())),
                    where.eq("destZoneCode", destZoneCode),
                    where.eq("transportAttribute", 1),
                    where.ne("transportWay", 3),
                    where.ne("isDelete", 1),
                    where.or(
                            where.isNull("invalidTime"),
                            where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date()))
                    )
            );
            if (sourceZone != null) {
                where.and(
                        where.or(
                                where.like("sourceZoneCode", "%" + sourceZone + "%"),
                                where.like("sourceZoneName", "%" + sourceZone + "%")
                        ),
                        null
                );
            }

            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<LineVoTable> findDestZoneCode(String destZoneCode) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.or(where.isNull("invalidTime"), where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date())));
            where.and().le("effectiveTime", DateHelper.getDateTimeFormate(new Date()));
            where.and().eq("destZoneCode", destZoneCode);
            //主用线路
            where.and().eq("transportAttribute", 1);
            //干线和支线
            where.and().ne("transportWay", 3);
            where.and().ne("isDelete", 1);
            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public List<LineVoTable> findByLineId(String lineId) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.eq("lineId", lineId);

            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public String findOrderByLatestTime() {
        String lastTime = null;
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder().orderBy("latestTime", false).limit(1L);
            List<LineVoTable> list = queryBuilder.query();
            if (list != null && list.size() > 0) {
                lastTime = list.get(0).getLatestTime();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lastTime;
    }

    /**
     * 只查询latestTime字段的最新时间，可提高查询速度
     */
    public String findOrderByLatestTime2() {
        String lastTime = null;
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            queryBuilder.selectColumns("latestTime");
            queryBuilder.orderBy("latestTime", false).limit(1L);
            List<LineVoTable> list = queryBuilder.query();
            if (list != null && list.size() > 0) {
                lastTime = list.get(0).getLatestTime();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lastTime;
    }

    /**
     * 查询上一站信息
     */
    public List<LineVoTable> queryPreStation(String preStation) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.like("lineCode", preStation).or().like("lineName", preStation);
            where.and().ne("isDelete", 1);
            List<LineVoTable> listUnloadScanTable = queryBuilder.query();
            return listUnloadScanTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 根据始发站和目的站查询线路信息
     *
     * @param sourceZoneCoed 始发站
     * @param destZoneCode   目的站
     */
    public LineVoTable findLine(String sourceZoneCoed, String destZoneCode) {
        try {
            QueryBuilder<LineVoTable, Long> queryBuilder = dao.queryBuilder();
            Where where = queryBuilder.where();
            where.or(where.isNull("invalidTime"), where.ge("invalidTime", DateHelper.getDateTimeFormate(new Date())));
            where.and().le("effectiveTime", DateHelper.getDateTimeFormate(new Date()));
            where.and().eq("sourceZoneCode", sourceZoneCoed);
            where.and().eq("destZoneCode", destZoneCode);
            //主用线路
            where.and().eq("transportAttribute", 1);
            //干线和支线
            where.and().ne("transportWay", 3);
            where.and().ne("isDelete", 1);
            LineVoTable lineVoTable = queryBuilder.queryForFirst();
            return lineVoTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据sql语句查询
     *
     * @param sql sql语句
     * @return list
     */
    public List<LineVoTable> queryDataBySql(String sql) {
        try {
            GenericRawResults<LineVoTable> results = dao.queryRaw(sql, new RawRowMapper<LineVoTable>() {
                @Override
                public LineVoTable mapRow(String[] columnNames, String[] resultColumns) {
                    LineVoTable table = new LineVoTable();
                    table.setId(Long.parseLong(resultColumns[0]));
                    table.setLineCode(resultColumns[1]);
                    table.setLineName(resultColumns[2]);
                    table.setSourceZoneCode(resultColumns[3]);
                    table.setSourceZoneName(resultColumns[4]);
                    table.setDestZoneCode(resultColumns[5]);
                    table.setDestZoneName(resultColumns[6]);
                    table.setTransportWay(resultColumns[7]);
                    table.setProductType(resultColumns[8]);
                    table.setAreaType(resultColumns[9]);
                    table.setBusinessModel(resultColumns[10]);
                    table.setTransportAttribute(resultColumns[11]);
                    table.setPerformCycle(resultColumns[12]);
                    table.setLineMileage(new BigDecimal(resultColumns[13]));
                    table.setTimeEffect(new BigDecimal(resultColumns[14]));
                    table.setEffectiveType(resultColumns[15]);
                    table.setInvalidTime(resultColumns[16]);
                    table.setNote(resultColumns[17]);
                    table.setCompCode(resultColumns[18]);
                    table.setCreater(resultColumns[19]);
                    table.setCreaterTime(resultColumns[20]);
                    table.setModifier(resultColumns[21]);
                    table.setModifierTime(resultColumns[22]);
                    table.setIsDelete(Integer.parseInt(resultColumns[23]));
                    table.setLatestTime(resultColumns[24]);
                    table.setRecordVersion(Long.parseLong(resultColumns[25]));
                    return table;
                }
            });
            List<LineVoTable> list = new ArrayList<>();
            Iterator<LineVoTable> iterator = results.iterator();
            while (iterator.hasNext()) {
                LineVoTable dictionary = iterator.next();
                list.add(dictionary);
            }
            return list;
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        return null;
    }
}
